// ---------------------------------------------------------
// Dung Vu's Export class
// Copyright (C) 2007 Dung Vu. All rights reserved.
// ---------------------------------------------------------

using System;
using System.Data;
using System.Text;
using System.Data.OleDb;
//using System.IO;
using System.Collections;
using System.Collections.Specialized; 

namespace common
{
	public class Export
	{
        //private static RKLib.ExportData.Export exportLib = new RKLib.ExportData.Export("Win");
		public enum Format : int {None=0,CSV = 1, Excel = 2,XML=3 }; // Export format enumeration			

        public static void ExportToDisk(CrystalDecisions.CrystalReports.Engine.ReportClass myReport, 
                                        CrystalDecisions.Shared.ExportFormatType expFormatType,
                                        string toFileName )
        {
            CrystalDecisions.Shared.ExportOptions exportOpts = new CrystalDecisions.Shared.ExportOptions();
            CrystalDecisions.Shared.DiskFileDestinationOptions diskOpts = CrystalDecisions.Shared.ExportOptions.CreateDiskFileDestinationOptions();

            exportOpts.ExportFormatType = expFormatType;
            exportOpts.ExportDestinationType = CrystalDecisions.Shared.ExportDestinationType.DiskFile;

            diskOpts.DiskFileName = toFileName;
            exportOpts.ExportDestinationOptions = diskOpts;

            myReport.Export(exportOpts);
        }

        
        private static string ReplaceSpecialSQLChars(string str)
        {
            str = str.Replace('\'', ' ');
            return str;
        }
        private static OleDbConnection OpenConnection(string excelFile)
        {
            OleDbConnection oleDbConn = new OleDbConnection();
            oleDbConn.ConnectionString = SysLibs.ExcelConnectionString(excelFile, true);
            try
            {
                oleDbConn.Open();
                return oleDbConn;
            }
            catch { }
            return null;
        }

        private int[] ColumnId(DataTable frTable, string[] columnName)
        {
            if (columnName != null) return null;
            int colId, colCount=0;
            int[] columnList = new int[columnName.Length];
            for (int idx = 0; idx < columnName.Length; idx++)
            {
                colId =  frTable.Columns.IndexOf(columnName[idx]);
                if (colId < 0) continue;
                columnList[colCount++] = colId;
            }

            int[] retColumnList = new int[colCount];
            for (int idx = 0; idx < colCount; idx++) retColumnList[idx] = columnList[idx];
            return retColumnList;
        }
        private string[] ColumnName(DataTable frTable,int[] columnList)
        {
            if (columnList != null) return null;
            string[] columName = new string[columnList.Length];
            for (int idx = 0; idx < columnList.Length;idx++)
            {
                columName[idx] = frTable.Columns[columnList[idx]].ColumnName;  
            }
            return columName;
        }

        private static bool CreateExcelFile(string excelFile, string sheetName, DataTable frTable, StringCollection columnList, StringCollection headerList)
        {
            //frTable.WriteXml(excelFile);
            //DataTable tmp = new DataTable();
            //tmp.ReadXml(excelFile);
            //return true;

            string SQLCmd = "";
            OleDbConnection oleDbConn = OpenConnection(excelFile);
            if (oleDbConn == null) return false;
            try
            {
                if (columnList == null)
                {
                    columnList = new StringCollection();
                    for (int idx = 0; idx < frTable.Columns.Count; idx++) columnList.Add(frTable.Columns[idx].ColumnName);
                }
                if (headerList == null)
                {
                    headerList = new StringCollection();
                    for (int idx = 0; idx < columnList.Count; idx++) headerList.Add(columnList[idx]);
                }
                string colName;
                for (int idx = 0; idx < columnList.Count; idx++)
                {
                    colName = columnList[idx];
                    SQLCmd += (SQLCmd == "" ? "" : ",") + "[" + headerList[idx] + "]";
                    switch (frTable.Columns[colName].DataType.ToString().Trim())
                    {
                        case "System.Float":
                        case "System.Double":
                        case "System.Decimal":
                        case "System.Int16":
                        case "System.Int32":
                        case "System.Int64":
                            SQLCmd += " decimal";
                            break;
                        case "System.DateTime":
                            SQLCmd += " date ";
                            break;
                        case "System.Boolean":
                            SQLCmd += " bit";
                            break;
                        default:
                            SQLCmd += " nvarchar";
                            break;
                    }
                }
                SQLCmd = "CREATE TABLE [" + sheetName + "] (" + SQLCmd + ");";
                OleDbCommand oleDbComm = new OleDbCommand(SQLCmd, oleDbConn);
                oleDbComm.ExecuteNonQuery();
                oleDbConn.Close();
                return true;
            }
            catch (Exception er)
            {
                SysLog.WriteLog(er);
                return false;
            }
            finally
            {
                if (oleDbConn != null && oleDbConn.State == ConnectionState.Open) oleDbConn.Close();
            }
        }

        public static bool ExportToExcel(DataView view, string excelFile, string sheetName, StringCollection columnList, StringCollection headerList)
        {
            return ExportToExcel(view.ToTable(), excelFile, sheetName, columnList, headerList);
        }

        // From https://epplus.codeplex.com/releases/view/118053
        public static bool ExportToExcel(DataTable frTable, string exceFileName, string sheetName, StringCollection columnList, StringCollection headerList)
        {
            //Delete existing file with same file name.
            if (common.fileFuncs.FileExist(exceFileName)) common.fileFuncs.FileRemove(exceFileName);

            DataTable dataTbl = frTable.Copy();
            dataTbl.PrimaryKey = null;

            StringCollection excludeColumnList = new StringCollection();
            if (columnList == null)
            {
                columnList = new StringCollection();
                for (int idx = 0; idx < frTable.Columns.Count; idx++) columnList.Add(frTable.Columns[idx].ColumnName);
            }
            else
            {
                //Remove un-neccessary columns
                for (int idx = 0; idx < dataTbl.Columns.Count; idx++)
                {
                    if (columnList.Contains(dataTbl.Columns[idx].ColumnName)) continue;
                    excludeColumnList.Add(dataTbl.Columns[idx].ColumnName);
                }
                foreach (string colName in excludeColumnList)
                {
                    dataTbl.Columns.Remove(colName);
                }
            }
            if (headerList == null)
            {
                headerList = new StringCollection();
                for (int idx = 0; idx < columnList.Count; idx++) headerList.Add(columnList[idx]);
            }

            System.IO.FileInfo newFile = new System.IO.FileInfo(exceFileName);

            //Step 1 : Create object of ExcelPackage class and pass file path to constructor.
            using (var package = new OfficeOpenXml.ExcelPackage(newFile))
            {
                //Step 2 : Add a new worksheet to ExcelPackage object and give a suitable name
                OfficeOpenXml.ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(sheetName);

                //Step 3 : Start loading datatable form A1 cell of worksheet.
                worksheet.Cells["A1"].LoadFromDataTable(dataTbl, true, OfficeOpenXml.Table.TableStyles.None);

                //Step 4 : (Optional) Set the file properties like title, author and subject
                package.Workbook.Properties.Title = SysLibs.GetAssemblyTitle();
                package.Workbook.Properties.Author = SysLibs.GetAssemblyProduct();
                package.Workbook.Properties.Subject = SysLibs.GetAssemblyDescription();

                //Step 5 : Save all changes to ExcelPackage object which will create Excel 2007 file.
                package.Save();
            }
            return true;
        }
        public static bool _ExportToExcel(DataTable frTable, string excelFile, string sheetName, StringCollection columnList, StringCollection headerList)
        {
            int rowId;
            OleDbConnection oleDbConn = null;
            try
            {
                if (columnList == null)
                {
                    columnList = new StringCollection();
                    for (int idx = 0; idx < frTable.Columns.Count; idx++) columnList.Add(frTable.Columns[idx].ColumnName);
                }
                if (headerList == null)
                {
                    headerList = new StringCollection();
                    for (int idx = 0; idx < columnList.Count; idx++) headerList.Add(columnList[idx]);
                }
                if (headerList.Count != columnList.Count)
                {
                    return false;
                }

                //Change all space in headers to '-'
                for (int idx = 0; idx < headerList.Count; idx++) headerList[idx] = headerList[idx].Replace(' ', '_');

                if (common.fileFuncs.FileExist(excelFile)) common.fileFuncs.FileRemove(excelFile);
                if (!CreateExcelFile(excelFile, sheetName, frTable, columnList, headerList)) return false;

                oleDbConn = OpenConnection(excelFile);
                if (oleDbConn == null) return false;

                string SQL_part1 = "";
                for (int idx = 0; idx < columnList.Count; idx++)
                {
                    SQL_part1 += (SQL_part1 == "" ? "" : ",") + "[" + headerList[idx] + "]";
                }
                SQL_part1 = "INSERT INTO [" + sheetName + "](" + SQL_part1 + ")";

                DateTime dt = DateTime.Today;
                string SQL_part2, tmp;
                OleDbCommand oleDbComm = new OleDbCommand();
                oleDbComm.Connection = oleDbConn;
                string colName;


                for (rowId = 0; rowId < frTable.Rows.Count; rowId++)
                {
                    SQL_part2 = "";
                    for (int idx = 0; idx < columnList.Count; idx++)
                    {
                        colName = columnList[idx];
                        tmp = frTable.Rows[rowId][colName].ToString().Trim();
                        switch (frTable.Columns[colName].DataType.ToString().Trim())
                        {
                            case "System.Float":
                            case "System.Double":
                            case "System.Decimal":
                            case "System.Int16":
                            case "System.Int32":
                            case "System.Int64":
                                double d = 0; double.TryParse(tmp, out d); tmp = StringLibs.Number2SqlString(d);
                                SQL_part2 += (SQL_part2 == "" ? "" : ",") + (tmp != "" ? tmp : "0"); ;
                                break;
                            case "System.DateTime":
                                if (DateTime.TryParse(tmp, out dt)) tmp = StringLibs.Date2SqlString(dt);
                                else tmp = "";
                                SQL_part2 += (SQL_part2 == "" ? "" : ",") + (tmp != "" ? "'" + tmp + "'" : "null");
                                break;
                            case "System.Boolean":
                                SQL_part2 += (SQL_part2 == "" ? "" : ",") + (tmp != "" ? tmp : "null");
                                break;
                            default:
                                tmp = ReplaceSpecialSQLChars(tmp);
                                SQL_part2 += (SQL_part2 == "" ? "" : ",") + "'" + tmp + "'";
                                break;
                        }
                    }
                    SQL_part2 = " VALUES (" + SQL_part2 + ")";
                    oleDbComm.CommandText = SQL_part1 + SQL_part2;
                    oleDbComm.ExecuteNonQuery();
                }
                return true;
            }
            catch (Exception er)
            {
                SysLog.WriteLog(er);
                return false;
            }
            finally 
            { 
                if ( oleDbConn != null) oleDbConn.Close();
            }
        }

        public static bool ExportToExcel(DataTable frTable, string excelFile, StringCollection columnList, StringCollection headerList)
        {
            return ExportToExcel(frTable, excelFile, "Sheet1", columnList, headerList);
        }

        public static bool ExportToExcel(DataTable frTable, string excelFile, string sheetName)
        {
            return ExportToExcel(frTable, excelFile, sheetName, null, null);
        }
        public static bool ExportToExcel(DataTable frTable, string excelFile)
        {
            return ExportToExcel(frTable, excelFile, "Sheet1", null, null);
        }
	}
}
